from pyecharts.charts import Pie
from pyecharts import options as opts
from sc.ZDao import connect_mysql
import pymysql


def generate_job_count_pie():
    # 连接数据库
    db = connect_mysql()
    if not db:
        print("数据库连接失败")
        return

    try:
        # 创建游标
        with db.cursor() as cur:
            # 查询指定城市的岗位数量
            sql = """
            SELECT 
                SUBSTRING_INDEX(job_loc, '·', 1) AS city,
                COUNT(*) AS job_count
            FROM jobs
            WHERE (job_name LIKE '%Java%' OR job_name LIKE '%java%')
              AND SUBSTRING_INDEX(job_loc, '·', 1) IN ('北京', '上海', '深圳', '杭州', '厦门')
            GROUP BY SUBSTRING_INDEX(job_loc, '·', 1)
            ORDER BY job_count DESC
            """
            print("执行SQL:", sql)

            cur.execute(sql)
            results = cur.fetchall()

            # 准备数据（按查询结果排序）
            data = []
            color_sequence = ['#5470C6', '#91CC75', '#FAC858', '#EE6666', '#73C0DE']

            # 确保颜色与城市对应（可根据需要调整颜色顺序）
            for i, row in enumerate(results):
                city = row['city']
                count = row['job_count']
                color = color_sequence[i % len(color_sequence)]  # 循环使用颜色
                data.append((city, count))
                print(f"城市: {city}, 岗位数: {count}")

            # 创建饼图
            pie = (
                Pie(init_opts=opts.InitOpts(width="800px", height="600px"))
                .add(
                    "",
                    data,
                    radius=["30%", "70%"],
                    center=["50%", "50%"],
                    label_opts=opts.LabelOpts(
                        formatter="{b}: {c}个",  # 直接显示数量
                        font_size=14,
                        font_weight="bold",
                        color="#333"
                    ),
                    itemstyle_opts=opts.ItemStyleOpts(
                        border_width=1,
                        border_color="#fff"
                    )
                )
                .set_colors(color_sequence)
                .set_global_opts(
                    title_opts=opts.TitleOpts(
                        title="各城市-岗位数量图",
                        pos_left="center",
                        title_textstyle_opts=opts.TextStyleOpts(
                            font_size=20,
                            color="#333",
                            font_weight="bold"
                        )
                    ),
                    legend_opts=opts.LegendOpts(
                        orient="horizontal",
                        pos_top="5%",
                        align="center",
                        textstyle_opts=opts.TextStyleOpts(
                            font_size=12
                        )
                    ),
                    tooltip_opts=opts.TooltipOpts(
                        trigger="item",
                        formatter="{a}<br/>{b}: {c}个 ({d}%)"
                    )
                )
            )

            # 渲染图表
            output_file = "pienum.html"
            pie.render(output_file)
            print(f"\n✅ 岗位数量饼图已生成: {output_file}")

    except pymysql.Error as e:
        print(f"数据库操作出错: {e}")
    finally:
        if db:
            db.close()


if __name__ == "__main__":
    generate_job_count_pie()